# coding=utf-8
import sys
import os
from os.path import abspath, dirname

sys.path.insert(0, abspath(dirname(__file__)))
import tkinter
from tkinter import *
import Fun
import xlwings as xw
import SummaryTool as ST

uiName = "SummaryExcel"
ElementBGArray = {}
ElementBGArray_Resize = {}
ElementBGArray_IM = {}

DATA_WIDGET_DICT = {
    'data_range': '',
    'condition_labels': '',
    'summary_labels': '',
    'index_column': '',
    'delete_zero': False
}


class UiDataController:
    WindowName = 'root'

    def __init__(self, ui_name):
        self.ui_name = ui_name
        self._window = None

    @property
    def window(self):
        if self._window is None:
            self._window = Fun.GetElement(self.ui_name, self.WindowName)
        return self._window

    def msg(self, text, title='提示', window_type='info'):
        Fun.MessageBox(text, title, window_type, self.window)

    def get_ui_data(self):
        data = {}
        for key in DATA_WIDGET_DICT:
            if isinstance(DATA_WIDGET_DICT[key], str):
                data[key] = Fun.GetText(self.ui_name, key)
            elif isinstance(DATA_WIDGET_DICT[key], bool):
                data[key] = Fun.GetCurrentValue(self.ui_name, key)
            elif isinstance(DATA_WIDGET_DICT[key], list):
                data[key] = Fun.GetAllRowTextList(self.ui_name, key)
            else:
                raise Exception('不支持的控件取值类型')
        if not self.check_ui_data(data):
            return False
        return data

    def check_ui_data(self, data):
        # 取出要验证的数据
        data_range = data.get('data_range', '')
        condition_labels = data.get('condition_labels', '')
        summary_labels = data.get('summary_labels', '')
        # 验证数据区域
        if not data_range:
            self.msg('请填写数据区域地址（包括表头），如A1:D8')
            return False
        if not ST.is_range_address(data_range):
            self.msg('请填写正确的数据区域地址（包括表头），如A1:D8')
            return False
        rng = xw.Range(data_range)
        start_row, start_col = rng[0].row, rng[0].column
        end_row, end_col = rng[-1].row, rng[-1].column
        if end_row - start_row < 2:
            self.msg('数据区域（包括表头）至少需要3行')
            return False
        if end_col - start_col < 2:
            self.msg('数据区域至少需要2列')
            return False
        # 验证汇总标签
        condition_list = condition_labels.replace('，', ',').split(',')
        if not self.is_in_data_range(condition_list, start_col, end_col, start_row):
            return False
        # 验证求和标签
        summary_list = summary_labels.replace('，', ',').split(',')
        if not self.is_in_data_range(summary_list, start_col, end_col, start_row):
            return False
        return True

    def is_in_data_range(self, addr_list, range_start_col, range_end_col, range_start_row):
        if not addr_list:
            self.msg('请填写“汇总标签”和“求和标签”，如A1:B1,C1')
            return False
        for addr in addr_list:
            if not ST.is_range_address(addr):
                self.msg(f'{addr} 填写错误，请填写正确的地址，如A1')
                return False
            rng = xw.Range(addr)
            start_row, start_col = rng[0].row, rng[0].column
            end_row, end_col = rng[-1].row, rng[-1].column
            if not (start_row == range_start_row and end_row == range_start_row):
                self.msg(f'{addr}填写错误，应确保它位于数据区域第一行')
                return False
            if not (start_col >= range_start_col and end_col <= range_end_col):
                self.msg(f'{addr}填写错误，应确保它位于数据区域第一列和最后一列之间')
                return False
        return True


UiData = UiDataController(uiName)
SummaryApp = ST.SummaryTool()


def get_excel_object():
    if not ST.is_excel_exist():
        UiData.msg('请先打开Excel或WPS程序再操作\n注意：WPS应设置为“多组件模式”，如何设置可百度', '提示', 'error')
        return False
    sheet = xw.sheets.active
    if not isinstance(sheet, xw.Sheet):
        UiData.msg('请选择一个Excel工作表再操作', '提示', 'error')
        return False
    return sheet


# Form 'Form_1's Load Event :
def Form_1_onLoad(uiName, threadings=0):
    Fun.SetWindowIco(uiName,'ExcelTool.ico')
    # 初始化控件
    Fun.SetCurrentValue(uiName, 'delete_zero', False)


def add_condition_label_onCommand(uiName, widgetName, threadings=0):
    sheet = get_excel_object()
    if not sheet:
        return
    addr = sheet.book.selection.address
    text = Fun.GetText(uiName, 'condition_labels')
    addr = text + ',' + addr if text else addr
    Fun.SetText(uiName, 'condition_labels', addr)


def get_data_range_onCommand(uiName, widgetName, threadings=0):
    sheet = get_excel_object()
    if not sheet:
        return
    addr = sheet.book.selection.address
    Fun.SetText(uiName, 'data_range', addr)


def add_summary_labels_onCommand(uiName, widgetName, threadings=0):
    sheet = get_excel_object()
    if not sheet:
        return
    addr = sheet.book.selection.address
    text = Fun.GetText(uiName, 'summary_labels')
    addr = text + ',' + addr if text else addr
    Fun.SetText(uiName, 'summary_labels', addr)


def summary_onCommand(uiName, widgetName, threadings=1):
    sheet = get_excel_object()
    if not sheet:
        return
    data = UiData.get_ui_data()
    if not data:
        return
    result = SummaryApp.summary(sheet.name, data)
    if isinstance(result, str):
        UiData.msg(result)
        return
    SummaryApp.write_result(result, sheet)


def help_onCommand(uiName, widgetName, threadings=0):
    Fun.StartUp('help.pdf')


def suggest_onCommand(uiName, widgetName, threadings=0):
    info = """
    更多实用工具，欢迎访问表易达Excel工具箱
    https://www.nianhua.fun
    """
    res = Fun.AskBox('是否访问该网站', info, Fun.GetElement(uiName, 'root'))
    if res:
        Fun.StartUp('https://www.nianhua.fun')
